/*
 * Copyright (C) 2012-2013 Michael L.R. Marques
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 * 
 * Contact: michaellrmarques@gmail.com
 */
package com.jm.jmsql.objects;

import com.jm.commons.cryptography.Crypto;
import com.jm.commons.logging.LogSystem;
import com.jm.commons.utils.Constants;
import com.jm.jmsql.objects.base.JMSqlObject;
import com.jm.jmsql.objects.base.Library;
import com.jm.jmsql.objects.events.DefinitionEvent;
import com.jm.jmsql.objects.events.DefinitionListener;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.Icon;
import javax.swing.ImageIcon;
import javax.swing.SwingWorker;
import javax.swing.event.EventListenerList;

/**
 *
 * @author Michael L.R. Marques
 */
public final class Definition extends JMSqlObject implements Library {
    
    //
    private static final int EMPTY = 0;
    //
    private String datasource;
    //
    private String username;
    //
    private String password;
    //
    private Connection connection;
    //
    private Statement statement;
    //
    private ResultSet resultSet;
    //
    private SQLException exception;
    //
    private boolean library;
    //
    private EventListenerList listeners;
    
    /**
     * 
     * @param name
     * @param database
     * @param datasource
     * @param username
     * @param password 
     */
    public Definition(Database database, String name, String datasource, String username, String password) {
        super(database, name);
        this.datasource = datasource;
        this.username = username;
        this.password = password;
        this.listeners = new EventListenerList();
    }
    
    /**
     * 
     */
    @Override
    public void load() {
        if (isLoaded()) {
            return;
        }
        if (isConnected()) {
            DatabaseMetaData dmd = getDatabaseMetaData();
            try {
                try (ResultSet results = dmd.getCatalogs()) {
                    while (results.next()) {
                        try {
                            add(new Catalog(this, results.getString(1)));
                        } catch (SQLException sqle) {
                            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Add a catalog to the definition", sqle);
                        }
                    }
                }
                if (super.isEmpty()) {
                    try (ResultSet results = dmd.getSchemas()) {
                        while (results.next()) {
                            try {
                                add(new Schema(this, results.getString(1)));
                            } catch (SQLException sqle) {
                                LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Add a schema to the definition", sqle);
                            }
                        }
                    }
                }
            } catch (SQLException sqle) {
                LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Building the schema list from the database meta data", sqle);
            }
            // If empty, add the tables, procedures and views to the definition
            if (super.isEmpty()) {
                add(new Tables(this));
                add(new Procedures(this));
                add(new Views(this));
                this.library = true;
            } else {
                this.library = false;
            }
        }
        super.load();
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public Icon getIcon() {
        return new ImageIcon(getClass().getClassLoader().getResource("com/jm/jmsql/icons/" + (isConnected() ? "connected" : "disconnected") + "_definition.png"));
    }
    
    /**
     * 
     * @return 
     */
    public Database getDatabase() {
        return (Database) getParent();
    }
    
    /**
     * 
     * @return 
     */
    public String getDatasource() {
        return this.datasource;
    }
    
    /**
     * 
     * @return 
     */
    public String getUsername() {
        return this.username;
    }
    
    /**
     * 
     * @return 
     */
    public String getPassword() {
        return this.password;
    }
    
    /**
     * 
     * @return 
     */
    public boolean isLibrary() {
        return this.library;
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public boolean isSortable() {
        return isConnected() &&
                    !isLibrary() &&
                        super.isSortable();
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public boolean isEmpty() {
        return isConnected() &&
                    super.isEmpty();
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public int size() {
        return isConnected() ? super.size() : 0;
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public Tables getTables() {
        return !isEmpty() ? (Tables) get(0) : new Tables(this);
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public Procedures getProcedures() {
        return !isEmpty() ? (Procedures) get(1) : new Procedures(this);
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public Views getViews() {
        return !isEmpty() ? (Views) get(2) : new Views(this);
    }
    
    /**
     * 
     */
    public void connect() {
        SwingWorker worker = new SwingWorker() {
            @Override
            protected Object doInBackground() throws Exception {
                DefinitionEvent event = fireConnecting();
                try {
                    connection = getDatabase().connect(getDatasource(), getUsername(), getPassword());
                    if (connection != null ||
                            !connection.isClosed()) {
                        fireConnectionSuccessful(event);
                    } else {
                        fireConnectionFailed(event);
                    }
                } catch (SQLException sqle) {
                    LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Create the connection to the database", sqle);
                    exception = sqle;
                    fireConnectionFailed(event);
                } finally {
                    fireConnected(event);
                }
                return event;
            }
        };
        worker.execute();
    }
    
    /**
     * 
     * @return 
     */
    public boolean disconnect() {
        try {
            DefinitionEvent event = fireDisconnecting();
            if (this.statement != null) {
                this.statement.close();
                this.statement = null;
            }
            this.connection.close();
            clear();
            fireDisconnected(event);
            return true;
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Disconnect the connection to the database", sqle);
            this.exception = sqle;
            return false;
        }
    }
    
    /**
     * 
     * @return
     */
    public boolean isConnected() {
        // 
        if (this.connection == null) {
            return false;
        }
        // 
        try {
            return !this.connection.isClosed();
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Is the connection open to the database", sqle);
            this.exception = sqle;
            return false;
        }
    }
    
    /**
     * 
     * @return 
     */
    public Connection getConnection() {
        return this.connection;
    }
    
    /**
     * 
     * @return 
     */
    public DatabaseMetaData getDatabaseMetaData() {
        try {
            return this.connection.getMetaData();
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Get the database meta data", sqle);
            this.exception = sqle;
            return null;
        }
    }
    
    /**
     * 
     * @return 
     * @param sql
     * @throws java.sql.SQLException
     */
    public boolean execute(String sql) throws SQLException {
        try {
            DefinitionEvent event = fireExecuting();
            try {
                this.statement = this.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            } catch (SQLException sqle) {
                this.statement = this.connection.createStatement();
                LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Create the SQL statement", sqle);
            }
            boolean executed = this.statement.execute(sql);
            if (executed) {
                this.resultSet = this.statement.getResultSet();
            }
            fireExecuted(event, sql, executed);
            return executed;
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Execute the SQL statement", sqle);
            this.exception = sqle;
            throw sqle;
        }
    }
    
    /**
     * 
     * @return 
     */
    public ResultSet getResults() {
        return this.resultSet;
    }
    
    /**
     * 
     * @return 
     */
    public int getRowCount() {
        if (this.resultSet == null) {
            return 0;
        }
        try {
            this.resultSet.last();
            try {
                return this.resultSet.getRow();
            } finally {
                getResults().beforeFirst();
            }
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Get the row count", sqle);
            this.exception = sqle;
        }
        try {
            int count = 0;
            try {
                while (this.resultSet.next()) {
                    count++;
                }
            } finally {
                this.resultSet.beforeFirst();
            }
            return count;
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Get the row count", sqle);
            this.exception = sqle;
        }
        try {
            return this.resultSet.getFetchSize();
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Get the row count", sqle);
            this.exception = sqle;
        }
        return 0;
    }
    
    /**
     * 
     * @return 
     */
    public int getUpdateCount() {
        try {
            return this.statement.getUpdateCount();
        } catch (SQLException sqle) {
            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Get the update count", sqle);
            this.exception = sqle;
            return 0;
        }
    }
    
    /**
     * 
     * @return 
     */
    public SQLException getException() {
        return this.exception;
    }
    
    /**
     * 
     * @return 
     */
    public String getFullException() {
        StringBuilder builder = new StringBuilder();
        builder.append("SQL State: ");
        builder.append(this.exception.getSQLState());
        builder.append(Constants.NEW_LINE);
        builder.append("Error Code: ");
        builder.append(this.exception.getErrorCode());
        builder.append(Constants.NEW_LINE);
        builder.append("Message: ");
        builder.append(this.exception.getMessage());
        builder.append(Constants.NEW_LINE);
        return builder.toString();
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public JMSqlObject.Type getObjectType() {
        return JMSqlObject.Type.DEFINITION;
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public int getColumnCount() {
        return isConnected() ? 7 : 6;
    }
    
    /**
     * 
     * @param index
     * @return 
     */
    @Override
    public String getColumnName(int index) {
        switch (index) {
            case 0: return "Name";
            case 1: return "Database";
            case 2: return "Datasource";
            case 3: return "Username";
            case 4: return "Password";
            case 5: return "Connected";
            case 6: return "Schemas";
            default: return null;
        }
    }
    
    /**
     * 
     * @param index
     * @return 
     */
    @Override
    public Object getColumn(int index) {
        switch (index) {
            case 0: return getName();
            case 1: return getParent().getName();
            case 2: return this.datasource;
            case 3: return this.username;
            case 4: return Crypto.decrypt(this.password);
            case 5: return isConnected();
            case 6: return size();
            default: return null;
        }
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public int hashCode() {
        return super.hashCode();
    }
    
    /**
     * 
     * @param that
     * @return 
     */
    @Override
    public boolean equals(Object that) {
        return super.equals(that) &&
                    that instanceof Definition &&
                        ((Definition) that).getDatasource().equals(getDatasource()) &&
                            ((Definition) that).getUsername().equals(getUsername()) &&
                                ((Definition) that).getPassword().equals(getPassword());
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public String toString() {
        return this.getName();
    }
    
    /**
     * 
     * @param o
     * @return 
     */
    @Override
    public int compareTo(JMSqlObject o) {
        return isConnected() ? (((Definition) o).isConnected() ? 0 : -1) : ((Definition) o).isConnected() ? 1 : super.compareTo(o);
    }
    
    /**
     * 
     * @param index
     * @return 
     */
    @Override
    public boolean isColumnEditable(int index) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
    
    /**
     * 
     * @param index
     * @param object 
     */
    @Override
    public void setColumn(int index, Object object) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
    
    /**
     * 
     * @param listener 
     */
    public void addDefinitionListener(DefinitionListener listener) {
        this.listeners.add(DefinitionListener.class, listener);
    }
    
    /**
     * 
     * @param listener 
     */
    public void removeDefinitionListener(DefinitionListener listener) {
        this.listeners.remove(DefinitionListener.class, listener);
    }
    
    /**
     * 
     * @return 
     */
    public DefinitionListener[] getDefinitionListeners() {
        return (DefinitionListener[]) this.listeners.getListeners(DefinitionListener.class);
    }
    
    /**
     * 
     * @return 
     */
    public DefinitionEvent fireConnecting() {
        DefinitionEvent event = new DefinitionEvent(this, DefinitionEvent.EventType.CONNECTING, System.currentTimeMillis());
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.connecting(event);
        }
        return event;
    }
    
    /**
     * 
     * @param connecting
     */
    public void fireConnected(DefinitionEvent connecting) {
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.connected(new DefinitionEvent(this, DefinitionEvent.EventType.CONNECTED, connecting.getStart(), System.currentTimeMillis()));
        }
    }
    
    /**
     * 
     * @param connecting
     */
    public void fireConnectionSuccessful(DefinitionEvent connecting) {
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.connectionSuccessful(new DefinitionEvent(this, DefinitionEvent.EventType.CONNECTION_SUCCESSFUL, connecting.getStart(), System.currentTimeMillis()));
        }
    }
    
    /**
     * 
     * @param connecting
     */
    public void fireConnectionFailed(DefinitionEvent connecting) {
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.connectionFailed(new DefinitionEvent(this, DefinitionEvent.EventType.CONNECTION_FAILED, connecting.getStart(), System.currentTimeMillis()));
        }
    }
    
    /**
     * 
     * @return 
     */
    public DefinitionEvent fireDisconnecting() {
        DefinitionEvent event = new DefinitionEvent(this, DefinitionEvent.EventType.DISCONNECTING, System.currentTimeMillis());
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.disconnecting(event);
        }
        return event;
    }
    
    /**
     * 
     * @param disconnecting
     */
    public void fireDisconnected(DefinitionEvent disconnecting) {
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.disconnected(new DefinitionEvent(this, DefinitionEvent.EventType.DISCONNECTED, disconnecting.getStart(), System.currentTimeMillis()));
        }
    }
    
    /**
     * 
     * @return 
     */
    public DefinitionEvent fireExecuting() {
        DefinitionEvent event = new DefinitionEvent(this, DefinitionEvent.EventType.EXECUTING, System.currentTimeMillis());
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.executing(event);
        }
        return event;
    }
    
    /**
     * 
     * @param executing
     * @param sql
     * @param select
     * @return 
     */
    public boolean fireExecuted(DefinitionEvent executing, String sql, boolean select) {
        for (DefinitionListener listener : getDefinitionListeners()) {
            listener.executed(new DefinitionEvent(this, DefinitionEvent.EventType.EXECUTED, sql, executing.getStart(), System.currentTimeMillis(), select));
        }
        return select;
    }
    
}
